﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Sol_Demo.Api.Models;

namespace Sol_Demo.Api.Service
{
    public class Service : IService
    {
        string connection;
        public Service(string Connection)
        {
            connection = Connection;
        }

        public async Task<IEnumerable<User>> Login(User model)
        {
            using (var conn = new SqlConnection(connection))
            {
                return await conn.QueryAsync<User>("select * from [User]  where LoginName=@LoginName  and PassWord=@PassWord", model);
            }
        }

        public int Register(User model)
        {
            using (var conn = new SqlConnection(connection))
            {
                return conn.Execute("INSERT INTO [dbo].[User]([ID],[LoginName],[PassWord]) VALUES  (@ID,@LoginName,@PassWord)", model);
            }
        }

        public int Validate(User model)
        {
            using (var conn = new SqlConnection(connection))
            {
                return conn.Query<User>("select * from [User]  where LoginName=@LoginName", model).ToList().Count;
            }
        }
        public IEnumerable<PT_ProblemType> Query(PT_ProblemType model)
        {
            string sql = "select * from [PT_ProblemType] where 1=1 ";
            if (!string.IsNullOrWhiteSpace(model.ID))
            {
                sql += "and charindex(@ID, ID) > 0";
            }
            if (!string.IsNullOrWhiteSpace(model.CPYW))
            {
                sql += "and charindex(@CPYW, CPYW) > 0";
            }
            if (!string.IsNullOrWhiteSpace(model.PROBLEMTYPE))
            {
                sql += "and charindex(@PROBLEMTYPE, PROBLEMTYPE) > 0";
            }
            if (!string.IsNullOrWhiteSpace(model.PROBLEMTYPESUB))
            {
                sql += "and charindex(@PROBLEMTYPESUB, PROBLEMTYPESUB) > 0";
            }
            if (!string.IsNullOrWhiteSpace(model.FLAWCode))
            {
                sql += "and charindex(@FLAWCode, FLAWCode) > 0";
            }
            sql += " order by ID desc";
            using (var conn = new SqlConnection(connection))
            {
                return conn.Query<PT_ProblemType>(sql, model);
            }
        }

        public int delete(PT_ProblemType model)
        {
            using (var conn = new SqlConnection(connection))
            {
                return conn.Execute("delete [PT_ProblemType] where ID=@ID", model);
            }
        }

        public int insertorupdate(PT_ProblemType model)
        {
            using (var conn = new SqlConnection(connection))
            {
                if (string.IsNullOrWhiteSpace(model.ID))
                {
                    return conn.Execute("INSERT INTO [PT_ProblemType]([CPYW],[PROBLEMTYPE],[PROBLEMTYPESUB],[FLAWCode]) VALUES  (@CPYW,@PROBLEMTYPE,@PROBLEMTYPESUB,@FLAWCode)", model);
                }
                else
                {
                    return conn.Execute("update  [PT_ProblemType] set CPYW=@CPYW,PROBLEMTYPE=@PROBLEMTYPE,PROBLEMTYPESUB=@PROBLEMTYPESUB,FLAWCode=@FLAWCode where ID=@ID", model);
                }
            }
        }
    }
}
